﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Follow.Utils;
using Follow.Model;
using System.Transactions;

namespace Follow.DataAccess
{
    public class UsuarioDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public UsuarioDAO()
        {
            conn = ConnectionUtils.getConnection();
        }

        public IList<Usuario> findAll()
        {
            IList<Usuario> usuario = new List<Usuario>();
            String sqlCommand;
            try
            {
                conn.Open();

                sqlCommand = " select * from usuario order by nome ";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Usuario u = new Usuario();
                    u.IDUsuario = reader.GetInt32(0);
                    u.Login = reader.GetString(1);
                    u.IDGrupo = reader.GetInt32(2);
                    u.Nome = reader.GetString(3);
                    u.Email = reader.GetString(4);
                    u.Senha = reader.GetString(5);

                    usuario.Add(u);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return usuario;
            
        }

        public IList<Usuario> findByName(String nome)
        {
            IList<Usuario> usuario = new List<Usuario>();
            String sqlCommand;
            try
            {
                conn.Open();

                sqlCommand = " select * from usuario where nome like '%" + nome + "%' order by nome";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Usuario u = new Usuario();
                    u.IDUsuario = reader.GetInt32(0);
                    u.Login = reader.GetString(1);
                    u.IDGrupo = reader.GetInt32(2);
                    u.Nome = reader.GetString(3);
                    u.Email = reader.GetString(4);
                    u.Senha = reader.GetString(5);
                    usuario.Add(u);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return usuario;
        }
        
        public void insert(Usuario usuario, int idUsuario)
        {
            String sqlCommand;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    sqlCommand = " insert into usuario (login, grupo_idgrupo, nome, email, senha) values " +
                                        " ('" + usuario.Login + "', " + usuario.IDGrupo + ", '" + usuario.Nome + "', '" + usuario.Email + "', '" + usuario.Senha + "') ";
                    cmd = new SqlCommand(sqlCommand, conn);

                    cmd.ExecuteNonQuery();
                    transaction.Complete();

                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }


            LogDAO log = new LogDAO();
            log.insert(idUsuario, sqlCommand, "Insert", "Usuarios");
        }
        
        public void update(Usuario usuario, int idUsuario)
        {
            String sqlCommand;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    sqlCommand = " update usuario set grupo_idgrupo = " + usuario.IDGrupo + ", nome = '" + usuario.Nome + "', email = '" + usuario.Email + "', " +
                                        " login = '" + usuario.Login + "', senha = '" + usuario.Senha + "' where idusuario = " + usuario.IDUsuario;
                    cmd = new SqlCommand(sqlCommand, conn);
                 
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idUsuario, sqlCommand, "Update", "Usuarios");
        }

        public Usuario findById(int idusuarioID)
        {
            Usuario u = new Usuario();
            String sqlCommand;
            try
            {
                conn.Open();
                sqlCommand = " select * from usuario where idusuario = @idusuario ";
                cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.Add(new SqlParameter("idusuario", idusuarioID));
                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    u.IDUsuario = reader.GetInt32(0);
                    u.Login = reader.GetString(1);
                    u.IDGrupo = reader.GetInt32(2);
                    u.Nome = reader.GetString(3);
                    u.Email = reader.GetString(4);
                    u.Senha = reader.GetString(5);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return u;
        }

        public void delete(int id, int idUsuario)
        {
            String sqlCommand;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    sqlCommand = " delete from usuario where idusuario = " + id;
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idUsuario, sqlCommand, "Delete", "Usuarios");
        }

        public IList<Usuario> findByLogin(String login, String senha)
        {
            String sqlCommand;
            IList<Usuario> user = new List<Usuario>();
            try
            {
                conn.Open();
                sqlCommand = " select * from usuario where login = @login and senha = @senha ";
                cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.Add(new SqlParameter("login", login));
                cmd.Parameters.Add(new SqlParameter("senha", senha));
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Usuario u = new Usuario();
                    u.IDUsuario = reader.GetInt32(0);
                    u.Login = reader.GetString(1);
                    u.IDGrupo = reader.GetInt32(2);
                    u.Nome = reader.GetString(3);
                    u.Email = reader.GetString(4);
                    u.Senha = reader.GetString(5);
                    user.Add(u);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return user;
        }

        public IList<Usuario> findByGrupo(Int32 id)
        {
            IList<Usuario> usuario = new List<Usuario>();
            String sqlCommand;
            try
            {
                conn.Open();
                sqlCommand = " select * from usuario where grupo_idgrupo = " + id;
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Usuario u = new Usuario();
                    u.IDUsuario = reader.GetInt32(0);
                    u.Login = reader.GetString(1);
                    u.IDGrupo = reader.GetInt32(2);
                    u.Nome = reader.GetString(3);
                    u.Email = reader.GetString(4);
                    u.Senha = reader.GetString(5);
                    usuario.Add(u);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return usuario;
        }
    }
}